package com.wanmait.myhouse.dao;

import com.wanmait.myhouse.util.DBHelper;
import com.wanmait.myhouse.vo.Mediator;
import com.wanmait.myhouse.vo.Pager;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class MediatorDAO {

    public Mediator findById(Integer id){//根据中介id查姓名密码地址邮箱手机号
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,mediator_name as mediatorName,mediator_pass as mediatorPass,mediator_phone as mediatorPhone, office_address as officeAddress from t_mediator where id=?";
        try {
          Mediator mediator = queryRunner.query(sql,new BeanHandler<>(Mediator.class),id);
          return mediator;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public int getTotal(){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select count(1) as total from t_mediator";
        try {
            Object o = queryRunner.query(sql, new ScalarHandler<>());
            int total = Integer.parseInt(o.toString());
            return (int) total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public List<Mediator> findByPageno(Pager pager){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,mediator_name as mediatorName,mediator_phone as mediatorPhone, mediator_regtime as mediatorRegtime,office_address as officeAddress,ban_login as banLogin from t_mediator order by mediatorRegtime desc limit ?,?";
        int start = (pager.getPageNO()-1)*pager.getPageSize();
        try {
            List<Mediator> mediatorList = queryRunner.query(sql, new BeanListHandler<>(Mediator.class), start, pager.getPageSize());
            return mediatorList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public Mediator mediatorByName(String mediatorName){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,mediator_name as mediatorName,mediator_phone as mediatorPhone, mediator_regtime as mediatorRegtime,office_address as officeAddress,ban_login as banLogin from t_mediator where mediator_name=?";
        try {
            Mediator mediator = queryRunner.query(sql, new BeanHandler<>(Mediator.class),mediatorName);
            return mediator;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public void banMediator(Integer id,Boolean ban){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "update t_mediator set ban_login=? where id=?";
        try {
            queryRunner.update(sql,ban,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    public void addAll(Mediator mediator){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "update t_mediator set mediator_name=? , mediator_pass=? , mediator_phone=? , head_img=? , email=? , office_address=? where id=?";
        try {
            queryRunner.update(sql,mediator.getMediatorName(),mediator.getMediatorPass(),mediator.getMediatorPhone(),mediator.getHeadImg(),mediator.getEmail(),mediator.getOfficeAddress(),mediator.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public Mediator findBYMediatorNameAndMediatorPass(Mediator mediator){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,mediator_name as mediatorName, mediator_pass as mediatorPass,mediator_phone as mediatorPhone, mediator_regtime as mediatorRegtime,head_img as headImg,office_address as officeAddress , ban_login as banLogin  from t_mediator where mediator_name=? and mediator_pass=?";
        try {
            Mediator mediator1 = queryRunner.query(sql, new BeanHandler<>(Mediator.class), mediator.getMediatorName(), mediator.getMediatorPass());
            return mediator1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    //添加
    public void add(Mediator mediator){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "insert into t_mediator (mediator_name,mediator_pass,head_img,ban_login,mediator_regtime) values(?,?,?,?,?)";
        try {
            queryRunner.update(sql,mediator.getMediatorName(),mediator.getMediatorPass(),mediator.getHeadImg(),mediator.getBanLogin(),mediator.getMediatorRegtime());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //查询中介用户名
    public Mediator FindByMediatorName(Mediator mediator){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,mediator_name as mediatorName  from t_mediator where mediator_name=?";
        try {
            Mediator mediatorName = queryRunner.query(sql, new BeanHandler<>(Mediator.class), mediator.getMediatorName());
            return mediatorName;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }



    public void gaimima(Mediator mediator){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());

        String sql="update t_mediator set mediator_pass=? where id=?";

        try {
            queryRunner.update(sql,mediator.getMediatorPass(),mediator.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


}
